﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Common;
using SYH.Config;
using System.Data;

namespace SYH.Public.DAL
{
    public class Public_MessagesReceivers
    {
        #region 查找收件
        /// <summary>
        /// 根据id查找收件
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Model.Public_MessagesReceivers GetReceiverById(string id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"select a.Id,a.MailId,a.ReceiverId,a.IsReader,a.TypeId,b.Contents,b.CreateTime from 
Public_WebMessageReceiver a inner join Public_WebMessage b on a.MailId = b.Id 
where a.IsDeleted = 0 and a.Id = @Id");
            SqlParameter parameter = new SqlParameter("@Id", SqlDbType.Int, 4);
            parameter.Value = id;
            SqlDataReader reader = Common.SqlHelper.ExecuteReader(WebConfig.SqlConnString, CommandType.Text, strSql.ToString(), parameter);
            Model.Public_MessagesReceivers model = new Model.Public_MessagesReceivers();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    model.Id = reader.GetInt32(reader.GetOrdinal("Id"));
                    model.MailId = reader.GetInt32(reader.GetOrdinal("MailId"));
                    model.ReceiverId = reader.GetInt32(reader.GetOrdinal("ReceiverId"));
                    model.IsReader = reader.GetInt32(reader.GetOrdinal("IsReader"));
                    model.TypeId = reader.GetInt32(reader.GetOrdinal("TypeId"));
                    model.Contents = reader.GetString(reader.GetOrdinal("Contents"));
                    model.CreateTime = reader.GetDateTime(reader.GetOrdinal("CreateTime"));
                }
                reader.Close();
                return model;
            }
            reader.Close();
            return null;
        }
        #endregion

        #region 收件箱中总消息或未读消息数
        /// <summary>
        /// 收件箱中总消息或未读消息数
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="IsTotal">true 收件箱总数。false 未读信息数</param>
        /// <param name="TypeId">0是非系统消息，1是系统消息</param>
        /// <returns></returns>
        public int GetTotalReceivedMessage(int UserId, bool IsTotal, int TypeId)
        {
            StringBuilder strSql = new StringBuilder();
            if (TypeId == 0)
            {
                strSql.Append("select COUNT(*) as ReceivedCount from Public_WebMessageReceiver where ReceiverId = @UserId and IsDeleted = 0 and TypeId in (0,11,12,13,17) ");
            }
            else
            {
                strSql.Append("select COUNT(*) as ReceivedCount from Public_WebMessageReceiver where ReceiverId = @UserId and IsDeleted = 0 and TypeId not in (0,11,12,13,17) ");
            }
            if (!IsTotal)
            {
                strSql.Append(" and IsReader = 0 ");
            }
            SqlParameter[] parameters = {
				new SqlParameter("@UserId", SqlDbType.Int,4),
            };
            parameters[0].Value = UserId;
            return Common.TypeParse.ObjectToInt(Common.SqlHelper.ExecuteScalar(WebConfig.SqlConnString, CommandType.Text, strSql.ToString(), parameters), 0);
        }
        #endregion

        #region 批量删除收件箱
        /// <summary>
        /// 批量删除收件箱
        /// </summary>
        /// <param name="idList"></param>
        /// <returns></returns>
        public int DeleteReceiverList(string idList)
        {
            string[] idArray = idList.Split(',');
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Public_WebMessageReceiver set IsDeleted = 1 where ");
            strSql.Append(" Id in (" + idList + ")");
            SqlParameter parameter = new SqlParameter("@IdList", SqlDbType.VarChar, 200);
            parameter.Value = idList;
            object obj = SqlHelper.ExecuteNonQuery(WebConfig.SqlConnString, CommandType.Text, strSql.ToString(), parameter);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        #endregion

        #region  收件箱
        /// <summary>
        /// 分页收件箱列表
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageCount"></param>
        /// <param name="recordCount"></param>
        /// <param name="userId"></param>
        /// <param name="typeStyle ">0是系统消息，1是收件箱消息</param>
        /// <param name="sqlWhere"></param>
        /// <returns></returns>
        public DataSet GetReceiverList(int pageSize, int pageIndex, out int pageCount, out int recordCount, int userId, int typeStyle, string sqlWhere)
        {
            string fields = @" a.Id,a.MailId,a.ReceiverId,a.IsReader,a.TypeId,b.Contents,b.Title,a.CreateTime ";
            string tableName = @" Public_WebMessageReceiver a inner join Public_WebMessage b on a.MailId = b.Id ";
            string strCondition = sqlWhere;
            strCondition += string.Format(" and a.ReceiverId = {0} and a.IsDeleted = 0 ", userId);
            if (!strCondition.Contains("TypeId"))
            {
                if (typeStyle == 0)
                {
                    strCondition += string.Format(" and a.TypeId in (1,2,3,4,5,6,7,8,9,10,14,15,16) ");
                }
                else if (typeStyle == 1)
                {
                    strCondition += string.Format(" and a.TypeId in (0,11,12,13,17) ");
                }
            }
            return Common.PublicSqlData.SimplePager(WebConfig.SqlConnString
                , tableName
                , fields
                , pageSize
                , pageIndex
                , "Id"
                , true
                , strCondition
                , out pageCount
                , out recordCount
                , 0);
        }
        #endregion

        #region 根据id集合批量修改为已读消息
        /// <summary>
        /// 根据id集合批量修改为已读消息
        /// </summary>
        /// <param name="idList"></param>
        /// <returns></returns>
        public void UpdateReaderList(string idList)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Public_WebMessageReceiver set IsReader = 1 where Id in ( ");
            strSql.Append(@"select item from dbo.f_split(@Id,',')");
            strSql.Append(" )");
            SqlParameter par = new SqlParameter("@Id", idList);
            object obj = SqlHelper.ExecuteNonQuery(WebConfig.SqlConnString, CommandType.Text, strSql.ToString(), par);
        }
        #endregion

        #region  收件箱
        /// <summary>
        /// 根据mailid查找发件人
        /// </summary>
        /// <param name="mailId"></param>
        /// <returns></returns>
        public DataSet SearchSender(int mailId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" SELECT User_CompanyInfo.CompanyName, User_SimplyInfo.Id, User_SimplyInfo.Name FROM Public_WebMessageSenders INNER JOIN User_CompanyInfo ON Public_WebMessageSenders.SenderId = User_CompanyInfo.UserId INNER JOIN User_SimplyInfo ON User_CompanyInfo.UserId = User_SimplyInfo.Id and MailId=@MailId ");
            SqlParameter parameter = new SqlParameter("@MailId", SqlDbType.Int, 4);
            parameter.Value = mailId;
            DataSet ds = Common.SqlHelper.ExecuteDataset(WebConfig.SqlConnString, CommandType.Text, strSql.ToString(), parameter);
            return ds;
        }
        #endregion

        #region 根据id修改为已读消息
        /// <summary>
        /// 根据id修改为已读消息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UpdateIsReader(string id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Public_WebMessageReceiver set IsReader = 1 where Id = @Id");
            SqlParameter[] parameters ={
                    new SqlParameter("@Id",SqlDbType.Int,4)
                                       };
            parameters[0].Value = id;
            object obj = SqlHelper.ExecuteNonQuery(WebConfig.SqlConnString, CommandType.Text, strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        #endregion
    }
}
